<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    
    <title>19. Powershell und Datenbanken &mdash; PowerShell fuer Systemintegratoren</title>
    
    <link rel="stylesheet" href="_static/sphinxdoc.css" type="text/css" />
    <link rel="stylesheet" href="_static/pygments.css" type="text/css" />
    
    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    './',
        VERSION:     '1.0',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true
      };
    </script>
    <script type="text/javascript" src="_static/jquery.js"></script>
    <script type="text/javascript" src="_static/underscore.js"></script>
    <script type="text/javascript" src="_static/doctools.js"></script>
    <script type="text/javascript" src="_static/translations.js"></script>
    <link rel="top" title="PowerShell fuer Systemintegratoren" href="index.html" />
    <link rel="next" title="20. PowerShell CustomObjects" href="powershell_custom.html" />
    <link rel="prev" title="18. PowerShell für ActiveDirectory" href="powershell_activedirectory.html" /> 
  </head>
  <body>
    <div class="related">
      <h3>Navigation</h3>
      <ul>
        <li class="right" style="margin-right: 10px">
          <a href="genindex.html" title="Allgemeiner Index"
             accesskey="I">Index</a></li>
        <li class="right" >
          <a href="powershell_custom.html" title="20. PowerShell CustomObjects"
             accesskey="N">weiter</a> |</li>
        <li class="right" >
          <a href="powershell_activedirectory.html" title="18. PowerShell für ActiveDirectory"
             accesskey="P">zurück</a> |</li>
        <li><a href="index.html">PS4SYS</a> &raquo;</li> 
      </ul>
    </div>
      <div class="sphinxsidebar">
        <div class="sphinxsidebarwrapper">
            <p class="logo"><a href="index.html">
              <img class="logo" src="_static/PowerShell_icon_small.png" alt="Logo"/>
            </a></p>
  <h3><a href="index.html">Inhalt</a></h3>
  <ul>
<li><a class="reference internal" href="#">19. Powershell und Datenbanken</a><ul>
<li><a class="reference internal" href="#mysql">19.1. MySQL</a></li>
<li><a class="reference internal" href="#mssql">19.2. MSSQL</a></li>
<li><a class="reference internal" href="#fragen">19.3. Fragen</a></li>
</ul>
</li>
</ul>

  <h4>Vorheriges Thema</h4>
  <p class="topless"><a href="powershell_activedirectory.html"
                        title="vorheriges Kapitel">18. PowerShell für ActiveDirectory</a></p>
  <h4>Nächstes Thema</h4>
  <p class="topless"><a href="powershell_custom.html"
                        title="nächstes Kapitel">20. PowerShell CustomObjects</a></p>
  <h3>Diese Seite</h3>
  <ul class="this-page-menu">
    <li><a href="_sources/powershell_datenbanken.txt"
           rel="nofollow">Quelltext anzeigen</a></li>
  </ul>
<div id="searchbox" style="display: none">
  <h3>Schnellsuche</h3>
    <form class="search" action="search.html" method="get">
      <input type="text" name="q" />
      <input type="submit" value="Los" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    <p class="searchtip" style="font-size: 90%">
    Geben Sie Suchbegriffe oder einen Modul-, Klassen- oder Funktionsnamen ein.
    </p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>

    <div class="document">
      <div class="documentwrapper">
        <div class="bodywrapper">
          <div class="body">
            
  <div class="section" id="powershell-und-datenbanken">
<h1>19. Powershell und Datenbanken<a class="headerlink" href="#powershell-und-datenbanken" title="Permalink zu dieser Überschrift">¶</a></h1>
<img alt="_images/net_data_architecture.png" src="_images/net_data_architecture.png" />
<div class="section" id="mysql">
<h2>19.1. MySQL<a class="headerlink" href="#mysql" title="Permalink zu dieser Überschrift">¶</a></h2>
<p>Für MySQL muss man den .NET-Datenbanktreiber installieren, den man auf der MySQL-Website herunterladen kann.</p>
<div class="highlight-sql"><pre>## MYSQL Connection
## This requires mysql connector net

## All variables will need changing to suit your environment
$server= "localhost 3306"
$username= "root"
$password= "yourpassword"
$database= "mydatabase"

## The path will need to match the mysql connector you downloaded
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Connector Net 6.0.2\Assemblies\MySQL.Data.dll")

function global:Set-SqlConnection ( $server = $(Read-Host "SQL Server Name"), $username = $(Read-Host "Username"), $password = $(Read-Host "Password"), $database = $(Read-Host "Default Database") ) {
        $SqlConnection.ConnectionString = "server=$server;user id=$username;password=$password;database=$database;pooling=false;Allow Zero Datetime=True;"
}

function global:Get-SqlDataTable( $Query = $(if (-not ($Query -gt $null)) {Read-Host "Query to run"}) ) {
        if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
        $SqlCmd = New-Object MySql.Data.MySqlClient.MySqlCommand $Query, $SqlConnection
        $SqlAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet) | Out-Null
        $SqlConnection.Close()
        return $DataSet.Tables[0]
}

Set-Variable SqlConnection (New-Object MySql.Data.MySqlClient.MySqlConnection) -Scope Global -Option AllScope -Description "Personal variable for Sql Query functions"
Set-SqlConnection $server $username $password $database

$mysqltest = Get-SqlDataTable 'SHOW STATUS'

echo $mysqltest



$global:Query = 'SELECT `field1`, `field2`, `field3` FROM table'
$mysqlresults = Get-SqlDataTable $Query

ForEach ($result in $mysqlresults){
        echo $($result.field1)
        echo $($result.field2)
        echo $($result.field3)
}</pre>
</div>
<p>Ein anderes Beispiel:</p>
<div class="highlight-c#"><pre>    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

    # DB-Verbindung aufbauen
    $strConnectionString = "server=strServer; port=3306; uid=strBenutzer; pwd=strKennwort; database=strDatenbank; Pooling=False"
    $objConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($strConnectionString)
    $objConnection.Open()

    # Objekte MySqlCommand und MySqlDataAdapter erzeugen
    $strStatement = "SELECT * FROM kunden"
    $objCommand = New-Object MySql.Data.MySqlClient.MySqlCommand($strStatement, $objConnection)
    $objDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($objCommand)

    # Dataset erzeugen
    $objDataSet = New-Object System.Data.DataSet
    $objDataAdapter.Fill($objDataSet) &gt; $null

    # Ergebnis ausgeben
    $Ergebnis = $objDataSet.Tables[0]
    $Ergebnis | Format-Table *

    ==============================================================
    #obiges Beispiel refactorisiert
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

    function db_connect([string]$server, [string]$user, [string]$password, [string]$db)
    {
            # DB-Verbindung aufbauen
            $strConnectionString = "server=$server; port=3306; uid=$user; pwd=$password; database=$db; Pooling=False;"
            Write-host $strConnectionString

            $objConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($strConnectionString)


            $objConnection.Open()
            return $objConnection
    }


    function db_select([string]$strstatement, $objconnection)
    {
            # Objekte MySqlCommand und MySqlDataAdapter erzeugen
            $objCommand = New-Object MySql.Data.MySqlClient.MySqlCommand($strStatement, $objConnection)
            $objDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($objCommand)

            # Dataset erzeugen
            $objDataSet = New-Object System.Data.DataSet
            $objDataAdapter.Fill($objDataSet) &gt; $null
            return $objDataSet.Tables[0]
    }


    $connection = db_connect "192.168.0.12" "root" "passwort" "mysql"
    $Ergebnis = db_select "select @@version" $connection


    # Ergebnis ausgeben




    $Ergebnis | Format-Table *

    ==============================================================

    Backup-Skript aller Datenbanken

    #http://www.codeproject.com/Tips/234492/MySQL-DB-backup-using-powershell-script


    #Step 1
    # Core settings - you will need to set these
    $mysql_server = "192.168.0.12"
    $mysql_user = "root"
    $mysql_password = "passwort"
    $backupstorefolder= "c:\BackupPath\"
    $dbName = "wikidb"


    #Step 2
    $pathtomysqldump = "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"

    #Step 3
    cls
    # Determine TodayÂ´s Date Day (monday, tuesday etc)
    $timestamp = Get-Date -format yyyyMMddHHmmss
    Write-Host $timestamp

    #Step 4


    #Step 5
    # Connect to MySQL database 'information_schema'
    [system.reflection.assembly]::LoadWithPartialName("MySql.Data")
    $cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
    $cn.ConnectionString = "SERVER=$mysql_server;DATABASE=information_schema;UID=$mysql_user;PWD=$mysql_password"
    $cn.Open()


    #Step 6
    # Query to get database names in asceding order
    $cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
    $sql = "SELECT DISTINCT CONVERT(SCHEMA_NAME USING UTF8) AS dbName, CONVERT(NOW() USING UTF8) AS dtStamp FROM SCHEMATA ORDER BY dbName ASC"
    $cm.Connection = $cn
    $cm.CommandText = $sql
    $dr = $cm.ExecuteReader()

    # Loop through MySQL Records
    while ($dr.Read())
    {
     # Start By Writing MSG to screen
     $dbname = [string]$dr.GetString(0)
     if($dbname -match $dbName)
     {
     write-host "Backing up database: " $dr.GetString(0)

     # Set backup filename and check if exists, if so delete existing
     $backupfilename = $timestamp + "_" + $dr.GetString(0) + ".sql"
     $backuppathandfile = $backupstorefolder + "" + $backupfilename
     If (test-path($backuppathandfile))
     {
     write-host "Backup file '" $backuppathandfile "' already exists. Existing file will be deleted"
     Remove-Item $backuppathandfile
     }

     # Invoke backup Command. /c forces the system to wait to do the backup
     cmd /c " `"$pathtomysqldump`" -h $mysql_server -u $mysql_user -p$mysql_password $dbname &gt; $backuppathandfile "
     If (test-path($backuppathandfile))
     {
     write-host "Backup created. Presence of backup file verified"
     }
     }




==============================================================
    #insert-statement in mysql

    function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {

    # Load MySQL .NET Connector Objects
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

    # Open Connection
    $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
    $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
    $conn.Open()
    $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
    return $conn

    }

    function WriteMySQLQuery($conn, [string]$query) {

            $command = $conn.CreateCommand()
            $command.CommandText = $query
            $RowsInserted = $command.ExecuteNonQuery()
            $command.Dispose()
            if ($RowsInserted) {
                            return $RowInserted
                    } else {
                            return $false
                    }
    }

    # setup vars
    $user = 'myuser'
    $pass = 'mypass'
    $database = 'mydatabase'
    $MySQLHost = 'database.server.com'

    # Connect to MySQL Database
    $conn = ConnectMySQL $user $pass $MySQLHost $database

    # Read all the records from table
    $query = 'INSERT INTO test (id,name,age) VALUES ("1","Joe","33")'
    $Rows = WriteMySQLQuery $conn $query
    Write-Host $Rows " inserted into database"</pre>
</div>
</div>
<div class="section" id="mssql">
<h2>19.2. MSSQL<a class="headerlink" href="#mssql" title="Permalink zu dieser Überschrift">¶</a></h2>
<div class="highlight-csharp"><pre>$SQLServer = "MySQLServer" #use Server\Instance for named SQL instances!
$SQLDBName = "MyDBName"
$SqlQuery = "select * from authors WHERE Name = 'John Simon'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

clear

$DataSet.Tables[0]</pre>
</div>
</div>
<div class="section" id="fragen">
<h2>19.3. Fragen<a class="headerlink" href="#fragen" title="Permalink zu dieser Überschrift">¶</a></h2>
<p>Ausgangspunkt für die Arbeit ist die Datei <strong>servers.txt</strong>, in denen die IP-Adressen der vorhandenen MySQL-Server ihres Systems gespeichert sind.
Weiterhin existiert die Datei <strong>users.txt</strong> mit allen erlaubten Users eines Systems.</p>
<ol class="arabic">
<li><p class="first">Zeigen Sie die Versionen der verwendeten MySQL-DB-Server</p>
<blockquote>
<div><dl class="docutils">
<dt>::</dt>
<dd><p class="first">SELECT version();
SELECT &#64;&#64;version;</p>
<p class="last">SHOW GLOBAL VARIABLES LIKE &#8216;%version%&#8217;;
mysql&gt; STATUS;</p>
</dd>
</dl>
</div></blockquote>
</li>
<li><p class="first">Erzeugen Sie die Datei <strong>users.txt</strong>, indem Sie die Informationen ihres DB-Servers abfragen. Alle User ihres Systems sollen darin gelistet werden.</p>
<blockquote>
<div><div class="highlight-python"><pre>SELECT * from mysql.user;</pre>
</div>
</div></blockquote>
</li>
<li><p class="first">Welche user haben globale Rechte</p>
</li>
<li><p class="first">Erzeugen Sie eine Datei user_grants.txt, in der die Rechte eines Users angezeigt werden. Vergleichen Sie diese Datei mit einer in der Vorwoche erzeugten Datei, um UNterschiede zu sehen (&#8220;Hinweis auf gehackten Rechner&#8221;)</p>
</li>
<li><p class="first">Löschen Sie alle User, die lediglich Connection-Rechte haben (usage)</p>
</li>
</ol>
</div>
</div>


          </div>
        </div>
      </div>
      <div class="clearer"></div>
    </div>
    <div class="related">
      <h3>Navigation</h3>
      <ul>
        <li class="right" style="margin-right: 10px">
          <a href="genindex.html" title="Allgemeiner Index"
             >Index</a></li>
        <li class="right" >
          <a href="powershell_custom.html" title="20. PowerShell CustomObjects"
             >weiter</a> |</li>
        <li class="right" >
          <a href="powershell_activedirectory.html" title="18. PowerShell für ActiveDirectory"
             >zurück</a> |</li>
        <li><a href="index.html">PS4SYS</a> &raquo;</li> 
      </ul>
    </div>
    <div class="footer">
        &copy; Copyright 2010, STE.
      Mit <a href="http://sphinx-doc.org/">Sphinx</a> 1.2b1 erstellt.
    </div>
  </body>
</html>